/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;
 import model.Model;
 import java.sql.CallableStatement; 
 import java.sql.Connection; 
 import java.sql.ResultSet; 
 import java.sql.SQLException; 
 import java.util.ArrayList; 
 import java.util.List; 
 import java.util.logging.Level; 
 import java.util.logging.Logger; 
/**
 *
 * @author Thanhnd
 */
public class ProductCategoryDAO extends Model{
     public int insertProductCategory(ProductCategory productcategory){
        int retVal = 0; 
        Connection conn = null;
         CallableStatement callable = null; 
        try { 
            conn = getConnection();
            callable = conn.prepareCall("{ call procProductCategory_add(?,?) }");
            callable.setInt(1, productcategory.getCategoryID());
            callable.setInt(2, productcategory.getProductID());
            retVal = callable.executeUpdate(); 
       } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (SQLException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (InstantiationException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (IllegalAccessException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } finally {
            try {
                callable.close();
                conn.close();
                } catch (SQLException ex) {
                   Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
       return retVal;
    }
    public int updateProductCategory(ProductCategory productcategory){
        int retVal = 0; 
        Connection conn = null;
         CallableStatement callable = null; 
        try { 
            conn = getConnection();
            callable = conn.prepareCall("{ call procProductCategory_update(?,?) }");
            callable.setInt(1, productcategory.getCategoryID());
            callable.setInt(2, productcategory.getProductID());
            retVal = callable.executeUpdate(); 
       } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (SQLException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (InstantiationException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (IllegalAccessException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } finally {
            try {
                callable.close();
                conn.close();
                } catch (SQLException ex) {
                   Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
       return retVal;
    }
    public int deleteProductCategory(ProductCategory productcategory){
        int retVal = 0; 
        Connection conn = null;
         CallableStatement callable = null; 
        try { 
           conn = getConnection();
            callable = conn.prepareCall("{ call procProductCategory_delete(?) }");
            callable.setInt(1, productcategory.getCategoryID());
            retVal = callable.executeUpdate(); 
       } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (SQLException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (InstantiationException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } catch (IllegalAccessException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
       } finally {
            try {
                callable.close();
                conn.close();
                } catch (SQLException ex) {
                   Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
       return retVal;
    }
    public List<ProductCategory> getAllProductCategory(){
        List<ProductCategory> list = new ArrayList<ProductCategory>(); 
        ResultSet rs = null; 
        Connection conn = null;
         CallableStatement callable = null; 
        try { 
            conn = getConnection();
            callable = conn.prepareCall("{ call procProductCategory_getall() }");
            rs = callable.executeQuery();
            while (rs.next()) {
                ProductCategory productcategory= new ProductCategory();
                productcategory.setCategoryID(rs.getInt(1));
                productcategory.setProductID(rs.getInt(2));
                list.add(productcategory);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                rs.close();
                callable.close();
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(ProductCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

}
